Imports System.Data.SqlClient
Imports System.Data
Imports CAPA_Entidad
Imports CAPA_Utilitarios
Public Class TrasladoDatos

    'Se crea una instancia unica para las conexiones
    Dim conex As Conexion = Conexion.getInstance()

    Function listarTrasladosPendientes(ByVal codPickeador As String) As DataSet
        Dim ds As New DataSet
        Dim con As SqlConnection = conex.obtenerConexion()
        Try
            con.Open()
            Dim query As String = "SELECT dadpu1.DADPU_Codigo, dadpu1.ART_Codigo, a.ART_Descripcion, um.UNM_Abreviatura, " & _
                                  "u.UBI_Descripcion, dadpu1.DADPU_Cantidad AS Cant, " & _
                                  "SUM(ISNULL(dadpu2.DADPU_Cantidad,0)) AS cantPick, dadpu1.DCA_Codigo, dadp.DAP_Codigo " & _
                                  "FROM Documento_Almacen_Detalle_pickeador dadp " & _
                                  "INNER JOIN Documento_Almacen_Detalle_pickeador_Ubicacion dadpu1 " & _
                                  "ON dadpu1.CIA = dadp.CIA AND dadpu1.SUC_Codigo = dadp.SUC_Codigo " & _
                                  "AND dadpu1.TDOC_Codigo = dadp.TDOC_Codigo AND dadpu1.DCA_Codigo = dadp.DCA_Codigo " & _
                                  "AND dadpu1.DADPU_Activo = '1' " & _
                                  "LEFT JOIN Documento_Almacen_Relacion dar " & _
                                  "ON dar.CIA_Salida = dadpu1.CIA AND dar.SUC_Codigo_Salida = dadpu1.SUC_Codigo " & _
                                  "AND dar.TDOC_Codigo_Salida = dadpu1.TDOC_Codigo AND dar.DCA_Codigo_Salida = dadpu1.DCA_Codigo " & _
                                  "AND dar.DADPU_CodigoSal = dadpu1.DADPU_Codigo AND dar.TDOC_Codigo_Ingreso = 'TI' " & _
                                  "LEFT JOIN Documento_Almacen_Detalle_pickeador_Ubicacion dadpu2 " & _
                                  "ON dadpu2.CIA = dar.CIA_Ingreso AND dadpu2.SUC_Codigo = dar.SUC_Codigo_Ingreso " & _
                                  "AND dadpu2.TDOC_Codigo = dar.TDOC_Codigo_Ingreso AND dadpu2.DCA_Codigo = dar.DCA_Codigo_Ingreso " & _
                                  "AND dadpu2.DADPU_Codigo = dar.DADPU_CodigoIng " & _
                                  "INNER JOIN Articulo a " & _
                                  "ON a.ART_Codigo = dadpu1.ART_Codigo AND a.ART_Activo = '1' " & _
                                  "INNER JOIN UnidadMedida um " & _
                                  "ON um.UNM_Codigo = a.UNM_Codigo AND um.UNM_Activo = '1' " & _
                                  "INNER JOIN Ubicacion u " & _
                                  "ON u.UBI_Codigo = dadpu1.UBI_Codigo AND u.UBI_Activo = '1' " & _
                                  "WHERE dadp.PICK_Codigo = @PICK AND dadp.TDOC_Codigo = 'TS' AND dadp.DADP_Activo = '1' " & _
                                  "GROUP BY  dadpu1.DADPU_Codigo, dadpu1.ART_Codigo, a.ART_Descripcion, um.UNM_Abreviatura, " & _
                                  "u.UBI_Descripcion, dadpu1.DADPU_Cantidad, dadpu1.DADPU_CantPick, dadpu1.DCA_Codigo, dadp.DAP_Codigo " & _
                                  "HAVING dadpu1.DADPU_Cantidad - SUM(ISNULL(dadpu2.DADPU_Cantidad,0)) <> 0"

            Dim comando As New SqlCommand(query, con)
            With comando
                .Parameters.AddWithValue("@PICK", codPickeador)
            End With
            Dim adapter As SqlDataAdapter = New SqlDataAdapter(comando)
            adapter.Fill(ds, "EnTraslado")
        Catch ex As Exception
            ds = Nothing
        Finally
            con.Close()
        End Try

        Return ds
    End Function

End Class
